﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using StudentMgmt.IDAL;
using StudentMgmt.Model;
using StudentMgmt.DBUtility;
using StudentMgmt.Model.DataContext;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Data;

namespace StudentMgmt.SQLServerDAL
{
    public class Student: IStudent   
    {
        public IList<StudentInfo> GetStudents()
        {
            StudentMgmtDataContext db = new StudentMgmtDataContext();
            var students = from s in db.Students
                           select s;

            return students.ToList<StudentInfo>();
        }

        public StudentInfo GetStudent(string studentId)
        {
            StudentMgmtDataContext db = new StudentMgmtDataContext();
            var student = db.Students.Single(s => s.StudentNo == studentId);

            return student;
        }
        public string GetCurrentMaxStuNo(string department_id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select max(s.StuNo) as maxStuNo ");
            builder.Append("from Student as s inner join Classes as c on s.ClassId = c.Class_Id ");
            builder.Append("where c.Department_Id = @deptId");

            SqlParameter[] parms = {
                new SqlParameter("@deptId",SqlDbType.Char)
            };

            parms[0].Value = department_id;

            return SqlHelper.RunSQLReturnString(builder.ToString(), parms);
        }

        public void AlterStudent(StudentInfo student)
        {
            StudentMgmtDataContext db = new StudentMgmtDataContext();
            var temp = db.Students.Single(s=> s.StudentNo== student.StudentNo);
            temp.Sex = student.Sex;
            temp.StudentName = student.StudentName;
            temp.NativePlace = student.NativePlace;
           // temp.ClassId = student.ClassId;
            temp.Birthday = student.Birthday;
            db.SubmitChanges();
        }
      
        public void InsertStudent(StudentInfo student)
        {
            StudentMgmtDataContext db = new StudentMgmtDataContext();

            db.Students.InsertOnSubmit(student);
            db.SubmitChanges();         
        }

         public void AlterStudentClassId(string StuNo,  string ClassId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update Student ");      
            builder.Append("  set ClassId ='"+ClassId+"'");
            builder.Append("  where StuNo='"+StuNo+"';");
            SqlHelper.RunSQL(builder.ToString());
        }
        public int QueryStudent(string stuNo, string stuName, string class_id, string department_id, out DataTable objTable)
        {
            objTable = new DataTable();
            StringBuilder builder = new StringBuilder();
            builder.Append("select S.StuNo as '学号', S.StuName as '学生姓名', case when S.Sex ='M' then '男' else '女' end '性别', S.Birthday as '出生日期', S.Native_Place as '籍贯', C.Class_Name as '所在班级', D.Department_Name as '所在院系' ");
            builder.Append("from Student as S inner join Classes as C on S.ClassId = C.Class_Id ");
            builder.Append("inner join Department as D on C.Department_Id = D.Department_Id ");
            builder.Append("where 1=1 ");
            if (stuNo.Length > 0)
            {
                builder.Append("and S.StuNo = '" + stuNo + "' ");
            }
            if (class_id.Length > 0)
            {
                builder.Append("and C.Class_Id = '" + class_id + "' ");
            }
            if (department_id.Length > 0)
            {
                builder.Append("and C.Department_Id = '" + department_id + "' ");
            }
            if (stuName.Length > 0)
            {
                builder.Append("and S.StuName like '%" + stuName + "%'");
            }

            int val = SqlHelper.RunSQLReturnDataTable(builder.ToString(), out objTable);
            return val;
        }
        public int QueryStudent(out DataTable objTable)
        {
            int val = 0;
            objTable = new DataTable();
            StringBuilder builder = new StringBuilder();
            builder.Append("select S.StuNo as '学号', S.StuName as '学生姓名', case when S.Sex ='M' then '男' else '女' end '性别', S.Birthday as '出生日期', S.Native_Place as '籍贯', C.Class_Name as '所在班级', D.Department_Name as '所在院系' ");
            builder.Append("from Student as S inner join Classes as C on S.ClassId = C.Class_Id ");
            builder.Append("inner join Department as D on C.Department_Id = D.Department_Id ");
            builder.Append("where 1=1 ");
            val = SqlHelper.RunSQLReturnDataTable(builder.ToString(), out objTable);
            return val;
        }

        public int QueryStudent(string StuNo,out DataTable objTable)
        {
            int val = 0;
            objTable = new DataTable();
            StringBuilder builder = new StringBuilder();
            builder.Append("select S.StuNo as '学号', S.StuName as '学生姓名', case when S.Sex ='M' then '男' else '女' end '性别', S.Birthday as '出生日期', S.Native_Place as '籍贯', C.Class_Name as '所在班级', D.Department_Name as '所在院系' ");
            builder.Append("from Student as S inner join Classes as C on S.ClassId = C.Class_Id ");
            builder.Append("inner join Department as D on C.Department_Id = D.Department_Id ");
            builder.Append("where 1=1 ");
            if (StuNo.Length > 0)
            {
                builder.Append("and S.StuNo = '" + StuNo + "' ");
            }
            val = SqlHelper.RunSQLReturnDataTable(builder.ToString(), out objTable);
            return val;
        }
    }
}
